import pandas as pd

from openpyxl import load_workbook
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side

import util.lib as lib
import util.mydecorator as mydecorator


@mydecorator.calTime
def job():
    # 加载 Excel 文件
    wb = load_workbook(lib.data_path + 'gb_sta.xlsx')

    # 颜色
    red_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')
    blue_fill = PatternFill(start_color='66B3FF', end_color='66B3FF', fill_type='solid')
    green_fill = PatternFill(start_color='C6EFCE', end_color='C6EFCE', fill_type='solid')
    yellow_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

    lplt10_rule = CellIsRule(operator='lessThanOrEqual', formula=[10], stopIfTrue=True, fill=blue_fill)
    lplt20_rule = CellIsRule(operator='between', formula=[10, 20], stopIfTrue=True, fill=green_fill)
    lpgt99_rule = CellIsRule(operator='greaterThanOrEqual', formula=[99], stopIfTrue=True, fill=red_fill)
    
    dis_pct_rule = CellIsRule(operator='lessThan', formula=[0], stopIfTrue=True, fill=green_fill)

    count_rule = CellIsRule(operator='between', formula=[0,2], stopIfTrue=True, fill=yellow_fill)

    for sheet_name in wb.sheetnames:

        # 获取当前 Sheet
        sheet = wb[sheet_name]

        # 冻结首行（第一行）
        sheet.freeze_panes = 'A2'  # 表示冻结 A2 单元格上方的行（即第一行）

        # 设置第一列的宽
        sheet.column_dimensions['A'].width = 30 
        sheet.column_dimensions['B'].width = 15 

        # lp
        sheet.conditional_formatting.add('M2:M50000', lplt10_rule)
        sheet.conditional_formatting.add('M2:M50000', lplt20_rule)
        sheet.conditional_formatting.add('M2:M50000', lpgt99_rule)

        sheet.conditional_formatting.add('Q2:Q50000', dis_pct_rule)

        sheet.conditional_formatting.add('R2:R50000', count_rule)


    wb.save(lib.data_path + 'gb_sta_v.xlsx')


if __name__ == '__main__':
    job()